package org.clipjoint.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.lang3.StringUtils;
import org.clipjoint.dao.GameShopDao;
import org.clipjoint.entity.Game;
import org.clipjoint.entity.KeyWord;
import org.clipjoint.entity.User;
import org.clipjoint.entity.dto.GameDto;
import org.clipjoint.util.DataSourceUtil;

import java.sql.SQLException;
import java.util.*;

public class GameShopDaoImpl implements GameShopDao {
    @Override
    public List<Game> getList(GameDto gameDto, int pricestart, int priceend) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select * from `game` where 1=1";
        List<Object> params = new ArrayList<>();

        if(gameDto.getType() != null){
            sql+=" and `type`= ? ";
            params.add(gameDto.getType().name());
        }

        if (pricestart >= 0 && priceend != 0){
            sql+=" and `price` BETWEEN ? AND ? ";
            params.add(pricestart);
            params.add(priceend);
        }
        if (StringUtils.isNotBlank(gameDto.getName())) {
            sql += " and name like ?";
            params.add("%" + gameDto.getName() + "%");
        }
        if ("折扣".equals(gameDto.getTopmenu())){
            sql+=" and `off` NOT IN(0) ";
        }
        if ("最新".equals(gameDto.getTopmenu())){
            sql += " ORDER BY `time` desc";
        }

        sql += " limit ?,?";
        params.add((gameDto.getPageNum() - 1) * gameDto.getPageSize());
        params.add(gameDto.getPageSize());
        List<Game> result = qr.query(sql, new BeanListHandler<>(Game.class), params.toArray());
        return result;
    }

    @Override
    public Long getCount(GameDto gameDto, int pricestart, int priceend) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select count(*) from `game` where 1=1";
        List<Object> params = new ArrayList<>();
        if(gameDto.getType() != null){
            sql+=" and `type`=? ";
            params.add(gameDto.getType().name());
        }
        if (pricestart >= 0 && priceend != 0){
            sql+=" and `price` BETWEEN ? AND ? ";
            params.add(pricestart);
            params.add(priceend);
        }
        if (StringUtils.isNotBlank(gameDto.getName())) {
            sql += " and name like ?";
            params.add("%" + gameDto.getName() + "%");
        }
        if ("折扣".equals(gameDto.getTopmenu())){
            sql+=" and `off` NOT IN(0) ";
        }
        Map<String, Object> query = qr.query(sql, new MapHandler(),params.toArray());
        return (Long)query.get("count(*)");
    }

    @Override
    public Game getOne(Game game) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select `game`.* , `publisher`.`name` as publisher_name ,  `publisher`.`logo` as publisher_logo from `game`,`publisher` where `publisher`.`id`=`game`.`publisher_id` and `game`.`id` = ?";
        Game result = qr.query(sql, new BeanHandler<>(Game.class), game.getId());
        return result;
    }

    @Override
    //加入愿望单
    public int addDream(Long id, Game game) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "INSERT INTO `dream`(`user_id` , `game_id` , `game_name` , `game_image` , `game_price` , `create_time`) VALUES(?,?,?,?,?,?)";
        int result = qr.update(sql, id, game.getId(), game.getName(), game.getImage(), game.getPrice(), new Date());
        return result;
    }

    @Override
    //购买
    public int buy(User user, Game game, String keyword) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "INSERT INTO `purchased`(`tran_number` ,`user_id`, `game_id` , `game_name`, `game_price`, `game_image`, `finish_time`, `keyword`) VALUES(?,?,?,?,?,?,?,?)";
        int result = qr.update(sql, (Math.random() *100000), user.getId(),game.getId(), game.getName(), game.getPrice(), game.getImage(),new Date(),keyword);
        return result;
    }

    @Override
    //更新用户余额
    public int update(User user) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "UPDATE `user` SET `account` = ? WHERE `id` = ?";
        int i = qr.update(sql, user.getAccount(), user.getId());
        return i;
    }

    @Override
    //用户购买成功删除，删除愿望单
    public void delDream(User user, Game game) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "delete from `dream` where `user_id` = ? and `game_id`= ?";
        qr.update(sql , user.getId(),game.getId());
    }

    @Override
    //获取秘钥
    public KeyWord getKey(Long gameid) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "select * from `keyword` where `game_id`= ? and `detail` = 0";
        KeyWord result = qr.query(sql, new BeanHandler<>(KeyWord.class), gameid);
        return result;
    }

    @Override
    public void updateKey(String keyword) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "UPDATE `keyword` SET `detail` = 1 WHERE `keyword` = ?";
        qr.update(sql, keyword);
    }

    @Override
    public void remove(Long id, Long gameid) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
        String sql = "delete from `dream` where `user_id` = ? and `game_id` = ?";
        qr.update(sql, id, gameid);
    }
}
